package jc.malpractice.service;


import cn.hutool.json.JSONObject;
import jc.malpractice.db.DbUtil;
import jc.malpractice.db.Help;

import java.sql.*;
import java.util.ArrayList;

public class EventService {

    public static JSONObject getItems(Integer pac_ward) {
        if (pac_ward == null) return null;
        Connection conn = DbUtil.getConn();
        if (conn == null) return null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        JSONObject jo = new JSONObject();

        try {
            preparedStatement = conn.prepareStatement(
                    "select * from jc_md_board_item where pac_ward=? order by seq");
            preparedStatement.setInt(1, pac_ward);
            resultSet = preparedStatement.executeQuery();
            ArrayList<JSONObject> arrayList = new ArrayList<>();

            while (resultSet.next()) {
                JSONObject jo1 = new JSONObject();
                jo1.set("id", resultSet.getInt("id"));
                jo1.set("name", resultSet.getString("name"));
                jo1.set("seq", resultSet.getInt("seq"));

                arrayList.add(jo1);
            }

            jo.set("code", 0);
            jo.set("data", arrayList);
            jo.set("msg", "白板数据分类查询成功");

            return jo;
        } catch (SQLException e) {
            try {
                conn.rollback();
            } catch (Exception ee) {
                System.out.println(ee.getMessage());
            }

            System.out.println(e.getMessage());
        } finally {
            try {
                if (resultSet != null) resultSet.close();
                if (preparedStatement != null) preparedStatement.close();
                conn.close();
            } catch (SQLException e) {
                System.out.println(e.getMessage());
            }
        }

        // sql错误
        jo.set("code", 2001);
        jo.set("msg", "白板数据分类查询失败");

        return jo;
    }

    public static JSONObject insertItem(Integer pac_ward, String name) {
        if (pac_ward == null) return null;
        Connection conn = DbUtil.getConn();
        if (conn == null) return null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        JSONObject jo = new JSONObject();
        Statement statement;

        try {
            conn.setAutoCommit(false);
            preparedStatement = conn.prepareStatement(
                    "insert into jc_md_board_item (pac_ward,name) values (?,?)");
            preparedStatement.setInt(1, pac_ward);
            preparedStatement.setString(2, name);
            preparedStatement.executeUpdate();

            statement = conn.createStatement();
            resultSet = statement.executeQuery("select last_insert_id() as id");
            Integer id = null;

            if (resultSet.next()) {
                id = resultSet.getInt(1);
            }

            if (id > 0) {
                int seq = 10000;
                resultSet = statement.executeQuery("select max(seq) as maxseq from jc_md_board_item  ");

                if (resultSet.next()) {
                    seq = resultSet.getInt("maxseq");

                    preparedStatement = conn.prepareStatement(
                            "update jc_md_board_item set seq=? where id=?");
                    preparedStatement.setInt(1, seq + 1);
                    preparedStatement.setInt(2, id);
                    preparedStatement.executeUpdate();
                }
            }
            conn.commit();

            jo.set("code", 0);
            jo.set("data", id);
            jo.set("msg", " 白板数据分类添加成功");

            return jo;
        } catch (SQLException e) {
            try {
                conn.rollback();
            } catch (Exception ee) {
                System.out.println(ee.getMessage());
            }

            System.out.println(e.getMessage());
        } finally {
            try {
                if (resultSet != null) resultSet.close();
                if (preparedStatement != null) preparedStatement.close();
                conn.close();
            } catch (SQLException e) {
                System.out.println(e.getMessage());
            }
        }

        // sql错误
        jo.set("code", 2002);
        jo.set("msg", "白板数据分类添加失败");

        return jo;
    }

    public static JSONObject updateItem(String name, Integer id) {
        Connection conn = DbUtil.getConn();
        if (conn == null) return null;
        PreparedStatement preparedStatement = null;
        JSONObject jo = new JSONObject();

        try {

            preparedStatement = conn.prepareStatement("UPDATE jc_md_board_item SET name = ? WHERE id = ?");
            preparedStatement.setString(1, name);
            preparedStatement.setInt(2, id);
            preparedStatement.executeUpdate();

            jo.set("code", 0);
            jo.set("msg", "白板分类修改成功");

            return jo;

        } catch (SQLException e) {
            try {
                conn.rollback();
            } catch (Exception ee) {
                System.out.println(ee.getMessage());
            }

            System.out.println(e.getMessage());
        } finally {
            try {
                if (preparedStatement != null) preparedStatement.close();
                conn.close();
            } catch (SQLException e) {
                System.out.println(e.getMessage());
            }
        }

        // sql错误
        jo.set("code", 2003);
        jo.set("msg", "白板数据分类更新失败");

        return jo;
    }

    /**
     * @param id
     * @param seq
     * @param up  为1 就是上移，其他下移
     * @return
     */
    public static JSONObject updateSeq(Integer id, Integer seq, Integer up) {

        Connection conn = DbUtil.getConn();
        if (conn == null) return null;
        ResultSet resultSet = null;
        JSONObject jo = new JSONObject();
        Statement statement;
        String verb = "<";
        if (up != null && up == 1) verb = ">";
        try {
            conn.setAutoCommit(false);

            statement = conn.createStatement();

            resultSet = statement.executeQuery(
                    "select id,seq from jc_md_board_item where seq" + verb + seq
                            + " order by seq " + (verb.equals(">") ? "  limit 1 " : " desc  limit 1"));
            if (resultSet.next()) {
                int seq1 = resultSet.getInt("seq");
                int id1 = resultSet.getInt("id");
                if (seq1 == seq) {
                    if (verb.equals(">")) {
                        seq1++;
                    } else {
                        seq++;
                    }
                }
                String sql1 = "update jc_md_board_item set seq=" + seq + " where id=" + id1,
                        sql2 = "update jc_md_board_item set seq=" + seq1 + " where id=" + id;

                statement.executeUpdate(sql1);
                statement.executeUpdate(sql2);
            }
            conn.commit();

            jo.set("code", 0);
            jo.set("data", id);
            jo.set("msg", "白板数据分类数据顺序操作成功");

            return jo;
        } catch (SQLException e) {
            try {
                conn.rollback();
            } catch (Exception ee) {
                System.out.println(ee.getMessage());
            }

            System.out.println(e.getMessage());

        } finally {
            try {
                if (resultSet != null) resultSet.close();
                conn.close();
            } catch (SQLException e) {
                System.out.println(e.getMessage());
            }
        }
        // sql错误
        jo.set("code", 2004);
        jo.set("msg", "白板数据分类数据顺序操作失败");

        return jo;
    }

    public static JSONObject delItem(Integer id) {

        Connection conn = DbUtil.getConn();
        if (conn == null) return null;
        PreparedStatement preparedStatement = null;
        JSONObject jo = new JSONObject();

        try {
            preparedStatement = conn.prepareStatement("delete from jc_md_board_item where id = ?");
            preparedStatement.setInt(1, id);
            preparedStatement.executeUpdate();

            jo.set("code", 0);
            jo.set("msg", "白板数据分类删除成功");

            return jo;
        } catch (SQLException e) {
            try {
                conn.rollback();
            } catch (Exception ee) {
                System.out.println(ee.getMessage());
            }

            System.out.println(e.getMessage());
        } finally {
            try {
                if (preparedStatement != null) preparedStatement.close();
                conn.close();
            } catch (SQLException e) {
                System.out.println(e.getMessage());
            }
        }

        // sql失败
        jo.set("code", 2005);
        jo.set("msg", "白板数据分类删除失败");

        return jo;
    }

    //上报事件 到数据库 。。
    public static boolean upload_event(String handler_name,String title,String content,String cuase,String date,String injury_level,String memo,String time_elapsed,String user_code,String user_name,String dept_code,String dept_name,String status,String handler_code,String handler_dept_code,String handler_dept) {
        Connection conn = DbUtil.getConn();
        System.out.println(conn);
        if (conn == null) return false;

        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;


        try {
//            handler_code,handler_dept_name,handler_dept_code,
            preparedStatement = conn.prepareStatement(
                    "INSERT INTO event(title,handler_name,content,cause,report_time,injury_level,memo,time_elapsed," +
                            "reporter_user_code,reporter_user_name,reporter_dept_code,reporter_dept_name,status,handler_code,handler_dept_code,handler_dept) " +
                            "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
            //第2-5,13-16.是新增数据项
            preparedStatement.setString(1,title);
            preparedStatement.setString(2,handler_name);
            preparedStatement.setString(3,content);
            preparedStatement.setString(4,cuase);
            preparedStatement.setString(5,date);
            preparedStatement.setString(6,injury_level);
            preparedStatement.setString(7,memo);
            preparedStatement.setString(8,time_elapsed);
            preparedStatement.setString(9,user_code);
            preparedStatement.setString(10,user_name);
            preparedStatement.setString(11,dept_code);
            preparedStatement.setString(12,dept_name);
            preparedStatement.setString(13,status);
            preparedStatement.setString(14,handler_code);
            preparedStatement.setString(15,handler_dept_code);
            preparedStatement.setString(16,handler_dept);

            int r = preparedStatement.executeUpdate();
            if(r==1) return true;
            return false;

        } catch (SQLException e) {
            try {
                //conn.rollback();
            } catch (Exception ee) {
                System.out.println(ee.getMessage());
            }

            System.out.println(e.getMessage());
        } finally {
            try {
                if (resultSet != null) resultSet.close();
                if (preparedStatement != null) preparedStatement.close();
                conn.close();
            } catch (SQLException e) {
                System.out.println(e.getMessage());
            }
        }
        return false;

    }

    //根据id搜索处理事件
    public static JSONObject selectHandleEvent(String handler_id) {
        Connection conn = DbUtil.getConn();
        System.out.println(conn);
        if (conn == null) return null;

        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        JSONObject jo = new JSONObject();

        try {

            System.out.println("idhsfgsdgesdrgdthfyjhgfhyjgfhjgjkyfhujkdghjao wei "+handler_id);
            preparedStatement = conn.prepareStatement(
                    "select * from event" +
                            " where handler_code=? and status = 2 or status = 3 ");
            preparedStatement.setString(1, handler_id);
            resultSet = preparedStatement.executeQuery();

            jo = Help.Result4Json(resultSet);
            return jo;
        } catch (SQLException e) {
            try {

            } catch (Exception ee) {
                System.out.println(ee.getMessage());
            }

            System.out.println(e.getMessage());
        } finally {
            try {
                if (resultSet != null) resultSet.close();
                if (preparedStatement != null) preparedStatement.close();
                conn.close();
            } catch (SQLException e) {
                System.out.println(e.getMessage());
            }
        }

        jo.set("code", 1002);
        jo.set("msg", "查找失败");

        return jo;
    }

    //根据id搜索上报事件
    public static JSONObject selectReportEvent(String reporter_id) {
        Connection conn = DbUtil.getConn();
        System.out.println(conn);
        if (conn == null) return null;

        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        JSONObject jo = new JSONObject();

        try {

            preparedStatement = conn.prepareStatement(
                    "select * from event" +
                            " where reporter_user_code=?");
            preparedStatement.setString(1, reporter_id);
            resultSet = preparedStatement.executeQuery();

            jo = Help.Result4Json(resultSet);
            return jo;
        } catch (SQLException e) {
            try {

            } catch (Exception ee) {
                System.out.println(ee.getMessage());
            }

            System.out.println(e.getMessage());
        } finally {
            try {
                if (resultSet != null) resultSet.close();
                if (preparedStatement != null) preparedStatement.close();
                conn.close();
            } catch (SQLException e) {
                System.out.println(e.getMessage());
            }
        }

        jo.set("code", 1002);
        jo.set("msg", "查找失败");

        return jo;
    }

    // 受理 事件
    public static JSONObject accept_event(String id) {
        Connection conn = DbUtil.getConn();
        System.out.println(conn);
        if (conn == null) return null;

        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        JSONObject jo = new JSONObject();

        try {
            preparedStatement = conn.prepareStatement("update event " +
                    "set status = 3 where id=?");
            preparedStatement.setString(1, id);
            if(preparedStatement.executeUpdate()!=0) {
                jo.set("code", 1000);
                jo.set("msg", "修改成功");
                return jo;
            }
        } catch (SQLException e) {
            try {
//                conn.rollback();
            } catch (Exception ee) {
                System.out.println(ee.getMessage());
            }

            System.out.println(e.getMessage());
        } finally {
            try {
                if (resultSet != null) resultSet.close();
                if (preparedStatement != null) preparedStatement.close();
                conn.close();
            } catch (SQLException e) {
                System.out.println(e.getMessage());
            }
        }

        jo.set("code", 1002);
        jo.set("msg", "受理失败");

        return jo;
    }

    // 审核 事件
    public static JSONObject handle_event(String id,String result) {
        Connection conn = DbUtil.getConn();
        System.out.println(conn);
        if (conn == null) return null;

        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        JSONObject jo = new JSONObject();

        try {
            preparedStatement = conn.prepareStatement("update event " +
                    "set status = 4,result=? where id=?");
            preparedStatement.setString(1, result);
            preparedStatement.setString(2, id);
            if(preparedStatement.executeUpdate()!=0) {
                jo.set("code", 1000);
                jo.set("msg", "修改成功");
                return jo;
            }
        } catch (SQLException e) {
            try {
//                conn.rollback();
            } catch (Exception ee) {
                System.out.println(ee.getMessage());
            }

            System.out.println(e.getMessage());
        } finally {
            try {
                if (resultSet != null) resultSet.close();
                if (preparedStatement != null) preparedStatement.close();
                conn.close();
            } catch (SQLException e) {
                System.out.println(e.getMessage());
            }
        }

        jo.set("code", 1002);
        jo.set("msg", "审核失败");

        return jo;
    }

    //查找 事件 status
    public static JSONObject event_status(String id) {
        Connection conn = DbUtil.getConn();
        System.out.println(conn);
        if (conn == null) return null;

        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        JSONObject jo = new JSONObject();

        try {

            preparedStatement = conn.prepareStatement(
                    "select * from event" +
                            " where id=? ");
            preparedStatement.setString(1, id);
            resultSet = preparedStatement.executeQuery();

            jo = Help.Result4Json(resultSet);
            return jo;
        } catch (SQLException e) {
            try {

            } catch (Exception ee) {
                System.out.println(ee.getMessage());
            }

            System.out.println(e.getMessage());
        } finally {
            try {
                if (resultSet != null) resultSet.close();
                if (preparedStatement != null) preparedStatement.close();
                conn.close();
            } catch (SQLException e) {
                System.out.println(e.getMessage());
            }
        }

        jo.set("code", 1002);
        jo.set("msg", "查找失败");

        return jo;
    }

    //选择 事件 ID
    public static JSONObject selectEventById(String id) {
        Connection conn = DbUtil.getConn();
        System.out.println(conn);
        if (conn == null) return null;

        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        JSONObject jo = new JSONObject();

        try {

            preparedStatement = conn.prepareStatement(
                    "select * from event" +
                            " where id=? ");
            preparedStatement.setString(1, id);
            resultSet = preparedStatement.executeQuery();

            jo = Help.Result4Json(resultSet);
            return jo;
        } catch (SQLException e) {
            try {

            } catch (Exception ee) {
                System.out.println(ee.getMessage());
            }

            System.out.println(e.getMessage());
        } finally {
            try {
                if (resultSet != null) resultSet.close();
                if (preparedStatement != null) preparedStatement.close();
                conn.close();
            } catch (SQLException e) {
                System.out.println(e.getMessage());
            }
        }

        jo.set("code", 1002);
        jo.set("msg", "查找失败");

        return jo;
    }

    // 更新 事件 暂存
    public static JSONObject updateEvent1(String id,String title,String content,String report_time,String time_elapsed,String handler_code,String handler_name,String dept_code,String dept_name,String cause,String injury_level,String memo) {
        Connection conn = DbUtil.getConn();
        System.out.println(conn);
        if (conn == null) return null;

        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        JSONObject jo = new JSONObject();

        try {
            preparedStatement = conn.prepareStatement("update event " +
                    "set title=?,content=?,report_time=?,time_elapsed=?,handler_code=?,handler_name=?,handler_dept_code=?,handler_dept=?,cause=?,injury_level=?,memo=?,status=1 where id=?");
            preparedStatement.setString(1, title);
            preparedStatement.setString(2, content);
            preparedStatement.setString(3, report_time);
            preparedStatement.setString(4, time_elapsed);
            preparedStatement.setString(5, handler_code);
            preparedStatement.setString(6, handler_name);
            preparedStatement.setString(7, dept_code);
            preparedStatement.setString(8, dept_name);
            preparedStatement.setString(9, cause);
            preparedStatement.setString(10, injury_level);
            preparedStatement.setString(11, memo);
            preparedStatement.setString(12, id);
            System.out.println(handler_code+" "+handler_name+" "+dept_code+" "+dept_name);
            if(preparedStatement.executeUpdate()!=0) {
                jo.set("code", 1000);
                jo.set("msg", "修改成功");
                return jo;
            }
        } catch (SQLException e) {
            try {
//                conn.rollback();
            } catch (Exception ee) {
                System.out.println(ee.getMessage());
            }

            System.out.println(e.getMessage());
        } finally {
            try {
                if (resultSet != null) resultSet.close();
                if (preparedStatement != null) preparedStatement.close();
                conn.close();
            } catch (SQLException e) {
                System.out.println(e.getMessage());
            }
        }

        jo.set("code", 1002);
        jo.set("msg", "修改失败");

        return jo;
    }

    // 更新 事件 提交
    public static JSONObject updateEvent2(String id,String title,String content,String report_time,String time_elapsed,String handler_code,String handler_name,String dept_code,String dept_name,String cause,String injury_level,String memo) {
        Connection conn = DbUtil.getConn();
        System.out.println(conn);
        if (conn == null) return null;

        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        JSONObject jo = new JSONObject();

        try {
            preparedStatement = conn.prepareStatement("update event " +
                    "set title=?,content=?,report_time=?,time_elapsed=?,handler_code=?,handler_name=?,handler_dept_code=?,handler_dept=?,cause=?,injury_level=?,memo=?,status=2 where id=?");
            preparedStatement.setString(1, title);
            preparedStatement.setString(2, content);
            preparedStatement.setString(3, report_time);
            preparedStatement.setString(4, time_elapsed);
            preparedStatement.setString(5, handler_code);
            preparedStatement.setString(6, handler_name);
            preparedStatement.setString(7, dept_code);
            preparedStatement.setString(8, dept_name);
            preparedStatement.setString(9, cause);
            preparedStatement.setString(10, injury_level);
            preparedStatement.setString(11, memo);
            preparedStatement.setString(12, id);
            System.out.println(handler_code+" "+handler_name+" "+dept_code+" "+dept_name);
            if(preparedStatement.executeUpdate()!=0) {
                jo.set("code", 1000);
                jo.set("msg", "修改成功");
                return jo;
            }
        } catch (SQLException e) {
            try {
//                conn.rollback();
            } catch (Exception ee) {
                System.out.println(ee.getMessage());
            }

            System.out.println(e.getMessage());
        } finally {
            try {
                if (resultSet != null) resultSet.close();
                if (preparedStatement != null) preparedStatement.close();
                conn.close();
            } catch (SQLException e) {
                System.out.println(e.getMessage());
            }
        }

        jo.set("code", 1002);
        jo.set("msg", "修改失败");

        return jo;
    }

    // 查找 管理员
    public static JSONObject sel_hander() {
        Connection conn = DbUtil.getConn();
        System.out.println(conn);
        if (conn == null) return null;

        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        JSONObject jo = new JSONObject();

        try {

            preparedStatement = conn.prepareStatement(
                    "select distinct *  from reporter ");

            resultSet = preparedStatement.executeQuery();

            jo = Help.Result2Json(resultSet);
            return jo;
        } catch (SQLException e) {
            try {
                //conn.rollback();
            } catch (Exception ee) {
                System.out.println(ee.getMessage());
            }

            System.out.println(e.getMessage());
        } finally {
            try {
                if (resultSet != null) resultSet.close();
                if (preparedStatement != null) preparedStatement.close();
                conn.close();
            } catch (SQLException e) {
                System.out.println(e.getMessage());
            }
        }


        return jo;
    }

    public static void main(String[] args) {
        System.out.println(selectHandleEvent("cw0002"));
    }

}
